﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class DashboardController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public IList<Dashboard> GetDashboardData(int year, Employee User
        , string sSearch, int PageNumber, int Datalength, string SortCol, string SortDirection, out int TotalData)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetDashboardData";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;
            command.Parameters.Add("@SEARCH", SqlDbType.VarChar).Value = sSearch;

            DataSet ds = GetTable(command);

            switch (SortDirection)
            {
                case "asc":
                    switch (SortCol)
                    {
                        case "Description":
                            ds.Tables[0].DefaultView.Sort = "Description ASC";
                            break;
                        case "Weight":
                            ds.Tables[0].DefaultView.Sort = "Weight ASC";
                            break;
                        case "Format":
                            ds.Tables[0].DefaultView.Sort = "Format ASC";
                            break;
                        case "UoMMax":
                            ds.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax ASC";
                            break;
                        case "UoMMin":
                            ds.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin ASC";
                            break;
                       

                    }
                    break;
                case "desc":
                    switch (SortCol)
                    {
                        case "Description":
                            ds.Tables[0].DefaultView.Sort = "Description DESC";
                            break;
                        case "Weight":
                            ds.Tables[0].DefaultView.Sort = "Weight DESC";
                            break;
                        case "Format":
                            ds.Tables[0].DefaultView.Sort = "Format DESC";
                            break;
                        case "UoMMax":
                            ds.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMax DESC";
                            break;
                        case "UoMMin":
                            ds.Tables[0].DefaultView.Sort = "DescriptionUoMDetailMin DESC";
                            break;
                    }
                    break;
            }


            DataTable dt = ds.Tables[0].DefaultView.ToTable();

            IList<Dashboard> result = new List<Dashboard>();

            if (dt.Rows.Count > 0)
            {
                //for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                for (int i = PageNumber; i < Datalength; i++)
                {
                    if (i < dt.Rows.Count)
                    {
                        Dashboard d = new Dashboard();
                        d.KPIAssignID = dt.Rows[i]["KPIAssignID"].ToString();
                        d.KPIDescription = dt.Rows[i]["Description"].ToString();
                        d.Weight = dt.Rows[i]["Weight"].ToString();
                        d.Format = dt.Rows[i]["UoMFormat"].ToString();
                        d.DescriptionUoMDetailMax = dt.Rows[i]["DescriptionUoMDetailMax"].ToString();
                        d.DescriptionUoMDetailMin = dt.Rows[i]["DescriptionUoMDetailMin"].ToString();
                        d.Jan = dt.Rows[i]["Jan"].ToString();
                        d.Feb = dt.Rows[i]["Feb"].ToString();
                        d.Mar = dt.Rows[i]["Mar"].ToString();
                        d.Apr = dt.Rows[i]["Apr"].ToString();
                        d.May = dt.Rows[i]["May"].ToString();
                        d.Jun = dt.Rows[i]["Jun"].ToString();
                        d.Jul = dt.Rows[i]["Jul"].ToString();
                        d.Aug = dt.Rows[i]["Aug"].ToString();
                        d.Sep = dt.Rows[i]["Sep"].ToString();
                        d.Oct = dt.Rows[i]["Oct"].ToString();
                        d.Nov = dt.Rows[i]["Nov"].ToString();
                        d.Dec = dt.Rows[i]["Dec"].ToString();
                        d.Total = dt.Rows[i]["Total"].ToString();
                        d.ScoreAchieved = dt.Rows[i]["ScoreAchieved"].ToString();
                        result.Add(d);
                    }
                    else
                    {
                        break;
                    }
                }
            }

            TotalData = dt.Rows.Count;
            return result;

        }


        public string GetTotalWeightKPI(int year, int employeeID)
        {
            string result = "0";

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetDashboardData_TotalWeight";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employeeID;


            result = GetSingleData(command);

            return result;
        }

        public string GetTotalScoreKPI(int year, int employeeID)
        {
            string result = "0";

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetDashboardData_TotalScore";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = employeeID;


            result = GetSingleData(command);

            return result;
        }

        public IList<DashboardDetail> GetDashboardDetailData(int year, int month, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetDashboardDetail";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = month;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;

            DataSet ds = GetTable(command);

            IList<DashboardDetail> result = new List<DashboardDetail>();

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DashboardDetail d = new DashboardDetail();
                    d.Description = ds.Tables[0].Rows[i]["Description"].ToString();
                    d.Target = ds.Tables[0].Rows[i]["TargetDescription"].ToString();
                    d.Realization = ds.Tables[0].Rows[i]["Realization"].ToString();
                    d.Score = ds.Tables[0].Rows[i]["Score"].ToString();
                    d.StatusUser = ds.Tables[0].Rows[i]["StatusUser"].ToString();
                    d.StatusVerificator = ds.Tables[0].Rows[i]["StatusVerificator"].ToString();
                    d.KPIAssignID = ds.Tables[0].Rows[i]["KPIAssignID"].ToString();
                    result.Add(d);
                }
            }

            return result;

        }

        public IList<DashboardRealization> GetDashboardRealization(int KPIAssignID, int year, Employee User)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_GetDashboardChartRealizationData";
            command.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = year;
            command.Parameters.Add("@USERID", SqlDbType.VarChar).Value = User.ID;
            command.Parameters.Add("@KPIASSIGNID", SqlDbType.VarChar).Value = KPIAssignID;

            DataSet ds = GetTable(command);

            IList<DashboardRealization> result = new List<DashboardRealization>();

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DashboardRealization d = new DashboardRealization();
                    d.Dates = ds.Tables[0].Rows[i]["Dates"].ToString();
                    d.Score = Convert.ToDecimal( ds.Tables[0].Rows[i]["Score"].ToString().Replace(",","."));
                    d.Max = Convert.ToInt32(ds.Tables[0].Rows[i]["Max"].ToString());
                    d.Min = Convert.ToInt32(ds.Tables[0].Rows[i]["Min"].ToString());
                    result.Add(d);
                }
            }

            return result;

        }
    }
}